﻿@page "/项目类型产值"
@layout MainLayout2
@rendermode InteractiveServer
@inject ToastService ToastService
@inject AuthenticationStateProvider customAuthenticationStateProvider
@inject SqlSugarScope _SqlSugarScope
@inject NavigationManager NavigationManager

<BootstrapBlazorRoot>
    <h3>项目类型产值</h3>
    <h3>计量单位:万元</h3>
    <Table TItem="项目类型产值DTO" RenderMode="TableRenderMode.Table" Items="@Items" IsStriped="true" IsBordered="true" IsFixedHeader="true">
        <TableColumns>
            <TableColumn Text="项目类型" @bind-Field="@context.合同类型" />
            <TableColumn FormatString="F2" Text="1月" @bind-Field="@context.Jan" />
            <TableColumn FormatString="F2" Text="2月" @bind-Field="@context.Feb" />
            <TableColumn FormatString="F2" Text="3月" @bind-Field="@context.Mar" />
            <TableColumn FormatString="F2" Text="4月" @bind-Field="@context.Apr" />
            <TableColumn FormatString="F2" Text="5月" @bind-Field="@context.May" />
            <TableColumn FormatString="F2" Text="6月" @bind-Field="@context.Jun" />
            <TableColumn FormatString="F2" Text="7月" @bind-Field="@context.Jul" />
            <TableColumn FormatString="F2" Text="8月" @bind-Field="@context.Aug" />
            <TableColumn FormatString="F2" Text="9月" @bind-Field="@context.Sep" />
            <TableColumn FormatString="F2" Text="10月" @bind-Field="@context.Oct" />
            <TableColumn FormatString="F2" Text="11月" @bind-Field="@context.Nov" />
            <TableColumn FormatString="F2" Text="12月" @bind-Field="@context.Dec" />
            <TableColumn FormatString="F2" Text="合计" @bind-Field="@context.YearTotal" />
        </TableColumns>
    </Table>
</BootstrapBlazorRoot>

@code {
    [NotNull]
    private List<项目类型产值DTO>? Items { get; set; }

    protected override async Task OnInitializedAsync()
    {
        查询();
    }
    // private void 查询()
    // {
    //     string sql = $@"select 合同类型,Convert(decimal(18,2),sum(本月)/10000) 本月,Convert(decimal(18,2),sum(本年)/10000) 本年 from (
    //                     select 合同类型,经理作业量*项目任务明细含税单价 本月,0 本年
    //                     from Bus_合同主表
    //                     join  Bus_合同分拆报工 on Bus_合同主表.guid = Bus_合同分拆报工.总包合同ID
    //                     where DATEDIFF(MONTH,施工开始时间,GETDATE()) = 0
    //                     union all
    //                     select 合同类型,0 本月,经理作业量*项目任务明细含税单价 本年
    //                     from Bus_合同主表
    //                     join  Bus_合同分拆报工 on Bus_合同主表.guid = Bus_合同分拆报工.总包合同ID
    //                     where DATEDIFF(YEAR,施工开始时间,GETDATE()) = 0
    //                     ) t group by 合同类型 ";
    //     Items = _SqlSugarScope.Ado.SqlQuery<项目类型产值DTO>(sql);
    // }

    private void 查询()
    {

        string sql = @"WITH MonthlySums AS (
                        SELECT
                           DATEPART(MONTH, 施工开始时间) AS [Month],
                            Convert(decimal(18,2),sum((经理作业量*项目任务明细含税单价))/10000)  AS [经理作业量合计],
		                    合同类型
                        FROM
                              Bus_合同主表
                                            join  Bus_合同分拆报工 on Bus_合同主表.guid = Bus_合同分拆报工.总包合同ID
                        WHERE
                            DATEPART(YEAR, 施工开始时间) = DATEPART(YEAR, getdate())
                        GROUP BY
                            DATEPART(MONTH, 施工开始时间),合同类型
                    ), Pivoted AS (
                        SELECT
	                    合同类型,
                            [1] AS [Jan],
                            [2] AS [Feb],
                            [3] AS [Mar],
                            [4] AS [Apr],
                            [5] AS [May],
                            [6] AS [Jun],
                            [7] AS [Jul],
                            [8] AS [Aug],
                            [9] AS [Sep],
                            [10] AS [Oct],
                            [11] AS [Nov],
                            [12] AS [Dec]
                        FROM
                            MonthlySums
                        PIVOT
                        (
                            SUM([经理作业量合计])
                            FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
                        ) AS PivotTable
                    )

                    SELECT
                        *,
                        (ISNULL(Jan, 0) + ISNULL(Feb, 0) + ISNULL(Mar, 0) + ISNULL(Apr, 0) + ISNULL(May, 0) + ISNULL(Jun, 0) + ISNULL(Jul, 0) + ISNULL(Aug, 0) + ISNULL(Sep, 0) + ISNULL(Oct, 0) + ISNULL(Nov, 0) + ISNULL(Dec, 0)) AS [YearTotal]
                    FROM
                        Pivoted;
                    ";
        Items = _SqlSugarScope.Ado.SqlQuery<项目类型产值DTO>(sql);
    }
}
